﻿using CpsyV2Web.Models;
using Longbow;
using Longbow.Caching;
using Longbow.Data;
using Longbow.ExceptionManagement;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace CpsyV2Web.DataAccess
{
    /// <summary>
    /// 用户管理
    /// </summary>
    public class PersonHelper
    {
        //缓存
        internal const string RetrievePersonDataKey = "PersonHelper-RetrievePerson";

        /// <summary>
        /// 根据ID查询用户信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static IEnumerable<Person> RetrievePerson(int Id = 0)
        {
            var imgURL = DictHelper.RetrieveTestPath() + DictHelper.RetrieveIconFolderPath().Substring(2);
            var ret = CacheManager.GetOrAdd(RetrievePersonDataKey, key =>
            {
                DateTime datetime = DateTime.Now;
                string sql = "SELECT id, Name,Sex,Age,Major,Image from psy_user";
                List<Person> Experts = new List<Person>();
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                try
                {
                    using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            Experts.Add(new Person()
                            {
                                ID = (int)reader[0],
                                PersonName = (string)reader[1],
                                PersonSex = reader[2] == System.DBNull.Value ? true : Convert.ToBoolean(reader[2]),
                                PersonAge = LgbConvert.ReadValue(reader[3], datetime),
                                PersonMajor = LgbConvert.ReadValue(reader[4], string.Empty),
                                PersonImg = LgbConvert.ReadValue(reader[5], string.Empty) == string.Empty ? string.Empty : imgURL + reader[5]
                            });
                        }
                    }
                }
                catch (Exception ex) { ExceptionManager.Publish(ex); }
                return Experts;
            });
            return Id == 0 ? ret : ret.Where(t => Id == t.ID);
        }

        /// <summary>
        /// 新增和修改用户信息
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public static bool SavePerson(Person p)
        {
            if (p == null) throw new ArgumentNullException("p");
            bool ret = false;
            string sql = p.ID == 0 ?
                "Insert Into psy_user (Name,Sex,Age,Major,Image) Values(@Name,@Sex,@Age,@Major,@Image)" :
                "update psy_user set Name=@Name,Sex=@Sex,Age=@Age,Image=@Image,Major=@Major from psy_user  where Id=@PersonId";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@PersonId", p.ID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@Name", DBAccess.ToDBValue(p.PersonName), ParameterDirection.Input));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@Sex", DBAccess.ToDBValue(p.PersonSex), ParameterDirection.Input));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@Age", DBAccess.ToDBValue(p.PersonAge), ParameterDirection.Input));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@Image", DBAccess.ToDBValue(p.PersonImg), ParameterDirection.Input));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@Major", DBAccess.ToDBValue(p.PersonMajor), ParameterDirection.Input));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
                ret = true;
                CacheCleanUtility.ClearCache(expertIds: p.ID == 0 ? string.Empty : p.ID.ToString());
            }
            catch (DbException ex)
            {
                ExceptionManager.Publish(ex);
            }
            return ret;
        }

        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static bool DeletePerson(string ids)
        {
            var ret = false;
            if (string.IsNullOrEmpty(ids) || ids.Contains("'")) return ret;
            string[] arr = ids.Split(',');
            for (int i = 0; i < arr.Length; i++)
            {
                try
                {
                    string sql = "Delete from psy_user where Id=@id";
                    using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                    {
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@id", arr[i]));
                        DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                        CacheCleanUtility.ClearCache(expertIds: arr[i]);
                        ret = true;
                    }
                }
                catch (Exception ex)
                {
                    ExceptionManager.Publish(ex);
                }
            }
            return ret;
        }

        /// <summary>
        /// 初始点击echarts图
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<Echarts> RetrieveAllData()
        {
            string sql = "select year(Age) ,COUNT(*) from psy_user group by year(Age) order by year(Age)";
            List<Echarts> allResults = new List<Echarts>();
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        Echarts result = new Echarts();
                        result.Age = LgbConvert.ReadValue(reader[0], 0);
                        result.count = LgbConvert.ReadValue(reader[1], 0);
                        allResults.Add(result);
                    }
                }
            }
            catch (Exception ex) { ExceptionManager.Publish(ex); }
            return allResults;
        }

        /// <summary>
        /// 点击echarts图中某一年
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<Echarts> RetrieveYearData(int year)
        {
            string sql = "select month(Age) ,COUNT(*) from psy_user where year(Age)=" + year + " group by month(Age) order by month(Age) ASC";
            List<Echarts> allResults = new List<Echarts>();
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        Echarts result = new Echarts();
                        result.Month = LgbConvert.ReadValue(reader[0], 0);
                        result.count = LgbConvert.ReadValue(reader[1], 0);
                        allResults.Add(result);
                    }
                }
            }
            catch (Exception ex) { ExceptionManager.Publish(ex); }
            return allResults;
        }

        /// <summary>
        /// 点击echarts图中某一月
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<Echarts> RetrieveMonthData(int year, int month)
        {
            string sql = "select day(Age) ,COUNT(*)  from psy_user where year(Age)=" + year + " and month(Age)=" + month + "group by day(Age) order by day(Age) ASC";
            List<Echarts> allResults = new List<Echarts>();
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        Echarts result = new Echarts();
                        result.Day = LgbConvert.ReadValue(reader[0], 0);
                        result.count = LgbConvert.ReadValue(reader[1], 0);
                        allResults.Add(result);
                    }
                }
            }
            catch (Exception ex) { ExceptionManager.Publish(ex); }
            return allResults;
        }
    }
}
